﻿using System;
using System.Data;
using MySql.Data.MySqlClient;


namespace DAL
{
    public class MYSQL : IDb
    {
        MySqlConnection conn = new MySqlConnection();

        public MYSQL(string serverName, string dbName, string userID, string pwd)
        {
            conn.ConnectionString = "Server=" + serverName + ";" +
                                    "Database=" + dbName + ";" +
                                    "Uid=" + userID + ";" +
                                    "Pwd=" + pwd + ";";

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public System.Data.DataTable ExecuteSelectCommand(IDbCommand command)
        {
            DataTable result = new DataTable();

            try
            {
                MySqlCommand mysqlCommand = (MySqlCommand)command;
                mysqlCommand.Connection = conn;
                mysqlCommand.Connection.Open();
                mysqlCommand.CommandType = CommandType.Text;
                MySqlDataReader mysqlReader = mysqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(mysqlReader);
                mysqlReader.Close();
                return result;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="selectStatement"></param>
        /// <returns></returns>
        public System.Data.DataTable ExecuteSelectCommand(string selectStatement)
        {
            DataTable result = new DataTable();
            try
            {
                conn.Open();
                MySqlCommand mysqlCommand = new MySqlCommand(selectStatement);
                mysqlCommand.CommandType = CommandType.Text;
                MySqlDataReader mySqlReader = mysqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(mySqlReader);
                mySqlReader.Close();
                return result;

            }
            catch (Exception)
            {

                throw;
            }
        }

        public System.Data.DataTable ExecuteSelectCommand(string selectStatement, string[,] parameters)
        {
            DataTable result = new DataTable();
            try
            {
                MySqlCommand mysqlCommand = new MySqlCommand(selectStatement);

                for (int i = 0; i < parameters.Length; i++)
                {
                    mysqlCommand.Parameters.AddWithValue(parameters[i, 0], parameters[0, i]); //parameters[name, value]
                }

                conn.Open();
                MySqlDataReader mysqlReader = mysqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(mysqlReader);
                mysqlReader.Close();
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}
